#!/bin/bash

APP=education
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

ads_user_stats="
insert overwrite table ${APP}.ads_user_stats
select * from ${APP}.ads_user_stats
union
select
    '$do_date' dt,
    t1.recent_days,
    new_user_count,
    active_user_count
from
(
    select
        recent_days,
        count(*) new_user_count
    from ${APP}.dwd_user_register_inc
    lateral view explode(`array`(1,7,30)) tmp as recent_days
    where dt>=date_add('$do_date',-recent_days+1)
    group by recent_days
)t1
join
(
    select
        recent_days,
        count(*) active_user_count
    from ${APP}.dws_user_user_login_td
    lateral view explode(`array`(1,7,30)) tmp as recent_days
    where dt = '$do_date'
    and login_date_last >= date_add('$do_date',-recent_days+1)
    group by recent_days
)t2
on t1.recent_days=t2.recent_days;
"

ads_course_review="
with
t1 as
(
    select
        1 recent_days,
        course_id,
        course_count_1d course_count,
        avg_review_starts_1d avg_review_starts
    from ${APP}dws_course_review_1d
    where dt ='$do_date'
    group by  course_id, course_count_1d, avg_review_starts_1d
    union all
    select
        recent_days,
        course_id,
        case recent_days
            when 7 then course_count_7d
            when 30 then course_count_30d
        end course_count,
        case recent_days
            when 7 then avg_review_starts_7d
            when 30 then avg_review_starts_30d
        end avg_review_starts
    from ${APP}dws_course_review_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt ='$do_date'
    group by recent_days,course_id,course_count_7d,avg_review_starts_7d,course_count_30d,avg_review_starts_30d
),
t2 as
(
    select
        1 recent_days,
        course_id,
        sum(`if`(review_stars=5,1,0))*100/count(user_id) review_favor_rate
    from ${APP}dws_course_review_1d
    where dt ='$do_date'
    group by course_id
    union all
    select
        recent_days,
        course_id,
        sum(`if`(review_stars=5,1,0))*100/count(user_id) review_favor_rate
    from ${APP}dws_course_review_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt ='$do_date'
    group by recent_days,course_id
)
insert overwrite table ads_course_review
select * from ${APP}ads_course_review
union
select
    '$do_date' dt,
    t1.course_id,
    t1.recent_days,
    avg_review_starts,
    course_count,
    review_favor_rate
from t1
join t2
on t1.recent_days=t2.recent_days
and t1.course_id=t2.course_id;
"

ads_traffic_stats_by_channel="
insert overwrite table ${APP}.ads_traffic_stats_by_channel
select *
from ${APP}.ads_traffic_stats_by_channel
union
select '$do_date'                                                        dt,
       recent_days,
       channel,
       cast(count(distinct (mid_id)) as bigint)                            uv_count,
       cast(avg(during_time_1d) / 1000 as bigint)                          avg_duration_sec,
       cast(avg(page_count_1d) as bigint)                                  avg_page_count,
       cast(count(*) as bigint)                                            sv_count,
       cast(sum(if(page_count_1d = 1, 1, 0)) / count(*) as decimal(16, 2)) bounce_rate
from ${APP}.dws_traffic_session_page_view_1d lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt >= date_add('$do_date', -recent_days + 1)
group by recent_days, channel;
"

ads_page_path="
insert overwrite table ${APP}.ads_page_path
select *
from ${APP}.ads_page_path
union
select '$do_date' dt,
       recent_days,
       source,
       nvl(target, 'null'),
       count(*)     path_count
from (
         select recent_days,
                concat('step-', rn, ':', page_id)          source,
                concat('step-', rn + 1, ':', next_page_id) target
         from (
                  select recent_days,
                         page_id,
                         lead(page_id, 1, null) over (partition by session_id,recent_days)          next_page_id,
                         row_number() over (partition by session_id,recent_days order by view_time) rn
                  from ${APP}.dwd_traffic_page_view_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
                  where dt >= date_add('$do_date', -recent_days + 1)
              ) t1
     ) t2
group by recent_days, source, target;
"

ads_user_change="
insert overwrite table ${APP}.ads_user_change
select *
from ${APP}.ads_user_change
union
select churn.dt,
       user_churn_count,
       user_back_count
from (
         select '$do_date' dt,
                count(*)     user_churn_count
         from ${APP}.dws_user_user_login_td
         where dt = '$do_date'
           and login_date_last = date_add('$do_date', -7)
     ) churn
         join
     (
         select '$do_date' dt,
                count(*)     user_back_count
         from (
                  select user_id,
                         login_date_last
                  from ${APP}.dws_user_user_login_td
                  where dt = '$do_date'
                    and login_date_last = '$do_date' --今日活跃的用户
              ) t1
                  join
              (
                  select user_id,
                         login_date_last login_date_previous
                  from ${APP}.dws_user_user_login_td
                  where dt = date_add('$do_date', -1) --找出今日活跃用户的上次活跃日期
              ) t2
              on t1.user_id = t2.user_id
         where datediff(login_date_last, login_date_previous) >= 8
     ) back
     on churn.dt = back.dt;
"

ads_user_action="
insert overwrite table ${APP}.ads_user_action
select '$do_date' dt,
       page.recent_days,
       home_count,
       good_detail_count,
       cart_count,
       order_count,
       payment_count
from (
         select 1                                        recent_days,
                sum(if(page_id = 'home', 1, 0))          home_count,
                sum(if(page_id = 'course_detail', 1, 0)) good_detail_count
         from ${APP}.dws_traffic_page_visitor_page_view_1d
         where dt = '$do_date'
           and page_id in ('home', 'course_detail')
         union all
         select recent_days,
                sum(if(page_id = 'home' and view_count > 0, 1, 0)),
                sum(if(page_id = 'course_detail' and view_count > 0, 1, 0))
         from (
                  select recent_days,
                         page_id,
                         case recent_days
                             when 7 then view_count_7d
                             when 30 then view_count_30d
                             end view_count
                  from ${APP}.dws_traffic_page_visitor_page_view_nd lateral view explode(array(7, 30)) tmp as recent_days
                  where dt = '$do_date'
                    and page_id in ('home', 'course_detail')
              ) t1
         group by recent_days
     ) page
         join
     (
         select 1        recent_days,
                count(*) cart_count
         from ${APP}.dws_trade_user_cart_add_1d
         where dt = '$do_date'
         union all
         select recent_days,
                sum(if(cart_count > 0, 1, 0))
         from (
                  select recent_days,
                         case recent_days
                             when 7 then cart_add_count_7d
                             when 30 then cart_add_count_30d
                             end cart_count
                  from ${APP}.dws_trade_user_cart_add_nd lateral view explode(array(7, 30)) tmp as recent_days
                  where dt = '$do_date'
              ) t1
         group by recent_days
     ) cart
     on page.recent_days = cart.recent_days
         join
     (
         select 1        recent_days,
                count(*) order_count
         from ${APP}.dws_trade_user_order_1d
         where dt = '$do_date'
         union all
         select recent_days,
                sum(if(order_count > 0, 1, 0))
         from (
                  select recent_days,
                         case recent_days
                             when 7 then order_count_7d
                             when 30 then order_count_30d
                             end order_count
                  from ${APP}.dws_trade_user_order_nd lateral view explode(array(7, 30)) tmp as recent_days
                  where dt = '$do_date'
              ) t1
         group by recent_days
     ) ord
     on page.recent_days = ord.recent_days
         join
     (
         select 1        recent_days,
                count(*) payment_count
         from ${APP}.dws_trade_user_payment_1d
         where dt = '$do_date'
         union all
         select recent_days,
                sum(if(order_count > 0, 1, 0))
         from (
                  select recent_days,
                         case recent_days
                             when 7 then payment_count_7d
                             when 30 then payment_count_30d
                             end order_count
                  from ${APP}.dws_trade_user_payment_nd lateral view explode(array(7, 30)) tmp as recent_days
                  where dt = '$do_date'
              ) t1
         group by recent_days
     ) pay
     on page.recent_days = pay.recent_days;
"

ads_source_turning="
insert overwrite table ${APP}.ads_source_turning
select * from ${APP}.ads_source_turning
union
select
    '$do_date' dt,
    recent_days,
    source_id,
    t2.source_site,
    nvl(order_total_amount,0),
    nvl(order_source_percent,0)
from
(
    select
        1 recent_days,
        source_id,
        order_total_amount_1d order_total_amount,
        order_source_percent_1d order_source_percent
    from ${APP}.dws_trade_source_order_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        source_id,
        case recent_days
                when 7 then order_total_amount_7d
                when 30 then order_total_amount_30d
        end order_total_amount,
        case recent_days
                when 7 then order_source_percent_7d
                when 30 then order_source_percent_30d
        end order_source_percent
    from ${APP}.dws_trade_source_order_nd
    lateral view explode(array(7,30)) tmp as recent_days
    where dt='$do_date'
)t1 left join ods_base_source_full t2 on t1.source_id=t2.id
"

ads_new_buyer_stats="
insert overwrite table ${APP}.ads_new_buyer_stats
select
    '$do_date',
    recent_days,
    order_count_new,
    pay_count_new
from
(
    select
        recent_days,
        sum(if(order_first_date>=date_add('$do_date',-recent_days+1),1,0)) order_count_new,
        sum(if(pay_first_date>=date_add('$do_date',-recent_days+1),1,0)) pay_count_new
    from ${APP}.dws_trade_user_first_td lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
)t1
"

ads_age_buyer_stats="
insert overwrite table ${APP}.ads_age_buyer_stats
select * from ${APP}.ads_age_buyer_stats
union
select
    '$do_date' dt,
    recent_days,
    ages,
    order_count_nage
from
(
    select
        ages,
        recent_days,
        sum(if(date_id>=date_add('$do_date',-recent_days+1),sums,0)) order_count_nage
    from
     (
       select
            date_id,
            ages,
            sum(counts) sums
from
(
           select
                ord.order_id,
                date_id,
                case
                     when age<20 then '20岁以下'
                     when age between 20 and 29 then '20岁-29岁'
                     when age between 30 and 39 then '30岁-39岁'
                     when age between 40 and 49 then '40岁-49岁'
                     else '50岁以上'
                end ages,
                count(*) counts
            from
            (
                select
                   distinct order_id,
                    user_id,
                    date_id
                from
                    ${APP}.dwd_trade_order_detail_inc

            ) ord left join
            (
                select
                    id,
                    year(current_date)-year(birthday) age
                from
                    ${APP}.dim_user_zip where dt='9999-12-31'
            )users on ord.user_id=users.id
            group by order_id,date_id,age
         )t1 group by date_id,ages
    )t2 lateral view explode(array(1,7,30)) tmp as recent_days
    group by recent_days,ages
)t3
"

ads_subject_order="
insert overwrite table ${APP}.ads_subject_order
select * from ${APP}.ads_subject_order
union
select
    '$do_date' dt,
    1 recent_days,
     subject_id,
    subject_name,
    sum(order_count_1d),
    sum(user_count_1d),
    sum(order_total_amount_1d)
from
    ${APP}.dws_trade_course_order_1d group by subject_id,subject_name,dt having dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    subject_id,
    subject_name,
    sum(if(recent_days=7,order_count_7d,order_count_30d) ),
    sum(if(recent_days=7,user_count_7d,user_count_30d)) ,
    sum(if(recent_days=7,order_total_amount_7d,order_total_amount_30d))
from
    ${APP}.dws_trade_course_order_nd lateral view explode(array(7,30)) tmp as recent_days
group by subject_id,subject_name,dt,recent_days
    having dt='$do_date'
"

ads_category_order="
insert overwrite table ads_category_order
select * from ${APP}.ads_category_order
union
select
    '$do_date' dt,
    1 recent_days,
     category_id,
    category_name,
    sum(order_count_1d),
    sum(user_count_1d),
    sum(order_total_amount_1d)
from
    ${APP}.dws_trade_course_order_1d group by category_id,category_name,dt having dt='$do_date'
union
select
    '$do_date' dt,
    recent_days,
    category_id,
    category_name,
    sum(if(recent_days=7,order_count_7d,order_count_30d) ),
    sum(if(recent_days=7,user_count_7d,user_count_30d)) ,
    sum(if(recent_days=7,order_total_amount_7d,order_total_amount_30d))
from
    ${APP}.dws_trade_course_order_nd lateral view explode(array(7,30)) tmp as recent_days
group by category_id,category_name,dt,recent_days
    having dt='$do_date'
"

ads_course_try_and_buy="
insert overwrite table ${APP}.ads_course_try_and_buy
select * ${APP}.from ads_course_try_and_buy
union
select
    '$do_date',
    a1.course_id,
    a1.course_name,
    a1.try_count,
    nvl(a2.order_count,0)/a1.try_count
from
(

    select
        course_id,
        course_name,
        count(*) try_count
    from
    ${APP}.dws_cource_try_buy_td where
    (order_time is null or order_time>first_time)
     and dt='$do_date'
     and first_time >date_add('$do_date',-6)
    group by course_id,course_name

)a1 left join
(
   select
        course_id,
        course_name,
        count(*) order_count
    from
    ${APP}.dws_cource_try_buy_td where
        order_time > first_time
        and dt='$do_date'
        and first_time >date_add('$do_date',-6)
     group by course_id,course_name
)a2 on a1.course_id=a2.course_id
"
ads_subject_try_and_buy="
insert overwrite table ${APP}.ads_subject_try_and_buy
select * from ${APP}.ads_subject_try_and_buy
union
select
    '$do_date',
    a1.subject_id,
    a1.subject_name,
    a1.try_count,
    nvl(a2.order_count,0)/a1.try_count
from
(

    select
        subject_id,
        subject_name,
        count(*) try_count
    from
    ${APP}.dws_cource_try_buy_td where
    (order_time is null or order_time>first_time)
     and dt='$do_date'
     and first_time >date_add('$do_date',-6)
    group by subject_id,subject_name

)a1 left join
(
   select
        subject_id,
        subject_name,
        count(*) order_count
    from
    ${APP}.dws_cource_try_buy_td where
        order_time > first_time
        and dt='$do_date'
        and first_time >date_add('$do_date',-6)
     group by subject_id,subject_name
)a2 on a1.subject_id=a2.subject_id

"

ads_category_try_and_buy="
insert overwrite table ${APP}.ads_category_try_and_buy
select * from ${APP}.ads_category_try_and_buy
union
select
    '$do_date',
    a1.category_id,
    a1.category_name,
    a1.try_count,
    nvl(a2.order_count,0)/a1.try_count
from
(

    select
        category_id,
        category_name,
        count(*) try_count
    from
    ${APP}.dws_cource_try_buy_td where
    (order_time is null or order_time>first_time)
     and dt='$do_date'
     and first_time >date_add('$do_date',-6)
    group by category_id,category_name

)a1 left join
(
   select
        category_id,
        category_name,
        count(*) order_count
    from
    ${APP}.dws_cource_try_buy_td where
        order_time > first_time
        and dt='$do_date'
        and first_time >date_add('$do_date',-6)
     group by category_id,category_name
)a2 on a1.category_id=a2.category_id
"

ads_order_by_province="
insert overwrite table ${APP}.ads_order_by_province
select * from ${APP}.ads_order_by_province
union
select
    '$do_date' dt,
    1,
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    order_count_1d,
    user_count_1d,
    order_total_amount_1d
from ${APP}.dws_trade_province_order_1d
where dt='$do_date'
union all
select
    '$do_date' dt,
    recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    if(recent_days=7,order_count_7d,order_count_30d),
    if(recent_days=7,user_count_7d,user_count_30d),
    if(recent_days=7,order_total_amount_7d,order_total_amount_30d)
from ${APP}.dws_trade_province_order_nd
lateral view explode(array(7,30)) tmp as recent_days
where dt='$do_date';
"

ads_trade_stats="
insert overwrite table ${APP}.ads_trade_stats
select * from ${APP}.ads_trade_stats
union
select
    '$do_date' dt,
    odr.recent_days,
    order_final_amount,
    order_count,
    order_user_count
from
(
    select
        1 recent_days,
        sum(order_final_amount_1d) order_final_amount,
        sum(order_count_1d) order_count,
        count(*) order_user_count
    from ${APP}.dws_trade_user_course_order_1d
    where dt='$do_date'
    union all
    select
        recent_days,
        sum(order_final_amount),
        sum(order_count),
        sum(if(order_count>0,1,0))
    from
    (
        select
            recent_days,
            case recent_days
                when 7 then order_final_amount_7d
                when 30 then order_final_amount_30d
            end order_final_amount,
            case recent_days
                when 7 then order_count_7d
                when 30 then order_count_30d
            end order_count
        from ${APP}.dws_trade_user_course_order_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by recent_days
)odr
"

ads_course_exam_stats_by_paper="
insert overwrite table ${APP}.ads_course_exam_stats_by_paper
(
  --1日
  select '$do_date' dt,
         1 recent_days,
         paper_id,
         sum(user_score_sum_1d)/sum(user_exam_count_1d) score_avg,
         sum(user_time_sum_1d)/sum(user_exam_count_1d) time_avg,
         count(user_id) user_count
  from ${APP}.dws_course_user_paper_1d
  where dt='$do_date'
  group by paper_id
  union all
  --7 30日
  select '$do_date' dt,
         recent_days,
         paper_id,
         `if`(recent_days=7,sum(user_score_sum_7d),sum(user_score_sum_30d))/`if`(recent_days=7,sum(user_exam_count_7d),sum(user_exam_count_30d)) score_avg,
         `if`(recent_days=7,sum(user_time_sum_7d),sum(user_time_sum_30d))/`if`(recent_days=7,sum(user_exam_count_7d),sum(user_exam_count_30d)) time_avg,
         nvl(count(distinct (`if`(`if`(recent_days=7,user_exam_count_7d,user_exam_count_30d)>0,user_id,null))),0) user_count
  from ${APP}.dws_course_user_paper_nd
  lateral view explode (`array`(7,30)) tmp as recent_days
  where dt='$do_date'
  group by recent_days,paper_id
);
"
ads_course_exam_stats_by_course="
insert overwrite table ${APP}.ads_course_exam_stats_by_course
(
  --1日
  select '$do_date' dt,
         1 recent_days,
         course_id,
         sum(user_score_sum_1d)/sum(user_exam_count_1d) score_avg,
         sum(user_time_sum_1d)/sum(user_exam_count_1d) time_avg,
         count(user_id) user_count
  from ${APP}.dws_course_user_paper_1d
  where dt='$do_date'
  group by course_id
  union all
  --7 30日
  select '$do_date' dt,
         recent_days,
         course_id,
         `if`(recent_days=7,sum(user_score_sum_7d),sum(user_score_sum_30d))/`if`(recent_days=7,sum(user_exam_count_7d),sum(user_exam_count_30d)) score_avg,
         `if`(recent_days=7,sum(user_time_sum_7d),sum(user_time_sum_30d))/`if`(recent_days=7,sum(user_exam_count_7d),sum(user_exam_count_30d)) time_avg,
         count(distinct (`if`(`if`(recent_days=7,user_exam_count_7d,user_exam_count_30d)>0,user_id,null))) user_count
  from ${APP}.dws_course_user_paper_nd
  lateral view explode (`array`(7,30)) tmp as recent_days
  where dt='$do_date'
  group by recent_days,course_id
);
"
ads_course_question_accuracy="
insert overwrite table ${APP}.ads_course_question_accuracy
(
  select '$do_date' dt,
         1 recent_days,
         question_id,
         cast((course_correct_count_1d/course_question_count_1d)*100 as decimal(16,2)) accuracy
  from ${APP}.dws_course_question_1d
  where dt='$do_date'
  union all
  select '$do_date' dt,
         recent_days,
         question_id,
         `if`(recent_days=7,cast((course_correct_count_7d/course_question_count_7d)*100 as decimal(16,2)),cast((course_correct_count_30d/course_question_count_30d)*100 as decimal(16,2))) accuracy
  from ${APP}.dws_course_question_nd
  lateral view explode (`array`(7,30)) tmp as recent_days
  where dt='$do_date'
);
"
ads_course_paper_grade_section="
insert overwrite table ${APP}.ads_course_paper_grade_section
(
  select '$do_date' dt,
         1 recent_days,
         paper_id,
         F_RANK_1d F_RANK,
         E_RANK_1d E_RANK,
         D_RANK_1d D_RANK,
         C_RANK_1d C_RANK,
         B_RANK_1d B_RANK,
         A_RANK_1d A_RANK
  from ${APP}.dws_course_user_paper_rank_1d
  where dt='$do_date'
  union all
  select '$do_date' dt,
         recent_days,
         paper_id,
         if(recent_days=7,F_RANK_7d,F_RANK_30d) F_RANK_1d,
         if(recent_days=7,E_RANK_7d,E_RANK_30d) E_RANK_1d,
         if(recent_days=7,D_RANK_7d,D_RANK_30d) D_RANK_1d,
         if(recent_days=7,C_RANK_7d,C_RANK_30d) C_RANK_1d,
         if(recent_days=7,B_RANK_7d,B_RANK_30d) B_RANK_1d,
         if(recent_days=7,A_RANK_7d,A_RANK_30d) A_RANK_1d
  from ${APP}.dws_course_user_paper_rank_nd
  lateral view explode (`array`(7,30)) tmp as recent_days
  where dt='$do_date'
);
"
ads_course_play_stats_by_chapter="
insert overwrite table ${APP}.ads_course_play_stats_by_chapter
(
  select '$do_date' dt,
         1 recent_days,
         chapter_id,
         sum(user_play_count_1d) play_count,
         sum(user_play_during_time_1d)/count(*) play_time_avg,
         count(*) user_count
  from ${APP}.dws_course_user_chapter_play_1d
  where dt='$do_date'
  group by chapter_id
  union all
  select '$do_date' dt,
         recent_days,
         chapter_id,
         sum(`if`(recent_days=7,user_play_count_7d,user_play_count_30d)) play_count,
         sum(`if`(recent_days=7,user_play_during_time_7d,user_play_during_time_30d))/sum(`if`(`if`(recent_days=7,user_play_count_7d,user_play_count_30d)>0,1,0)) play_time_avg,
         sum(`if`(`if`(recent_days=7,user_play_count_7d,user_play_count_30d)>0,1,0)) user_count
  from ${APP}.dws_course_user_chapter_play_nd
  lateral view explode (`array`(7,30)) tmp as recent_days
  where dt='$do_date'
  group by recent_days,chapter_id
);
"
ads_course_play_stats_by_course="
insert overwrite table ${APP}.ads_course_play_stats_by_course
(
  select '$do_date' dt,
         1 recent_days,
         course_id course_id,
         sum(user_play_count_1d) play_count,
         sum(user_play_during_time_1d)/count(*) play_time_avg,
         count(*) user_count
  from ${APP}.dws_course_user_chapter_play_1d
  where dt='$do_date'
  group by course_id
  union all
  select '$do_date' dt,
         recent_days,
         course_id course_id,
         sum(`if`(recent_days=7,user_play_count_7d,user_play_count_30d)) play_count,
         sum(`if`(recent_days=7,user_play_during_time_7d,user_play_during_time_30d))/sum(`if`(`if`(recent_days=7,user_play_count_7d,user_play_count_30d)>0,1,0)) play_time_avg,
         sum(`if`(`if`(recent_days=7,user_play_count_7d,user_play_count_30d)>0,1,0)) user_count
  from ${APP}.dws_course_user_chapter_play_nd
  lateral view explode (`array`(7,30)) tmp as recent_days
  where dt='$do_date'
  group by recent_days,course_id
);
"
ads_course_complete_stats_by_course="
insert overwrite table ${APP}.ads_course_complete_stats_by_course
select *
from (
  select '$do_date' dt,
         1            recent_days,
         course_id,
         `if`(count(distinct user_id)<sum(course_is_completed),count(distinct user_id),sum(course_is_completed))
  from ${APP}.dws_course_user_course_com_rec_td
  where dt='$do_date'
  group by user_id,course_id
  union all
  select '$do_date' dt,
         7 recent_days,
         course_id,
         `if`(count(distinct user_id)<sum(flag),count(distinct user_id),sum(flag))
  from (
    select user_id,
           course_id,
           `if`(complete_date is not null and complete_date >= date_add('$do_date',-6),1,0) flag
    from ${APP}.dws_course_user_course_com_rec_td
    where dt='$do_date'
  )t1
  group by user_id,course_id
  union all
  select '$do_date' dt,
         30 recent_days,
         course_id,
         `if`(count(distinct user_id)<sum(flag),count(distinct user_id),sum(flag))
  from (
    select user_id,
           course_id,
           `if`(complete_date is not null and complete_date >= date_add('$do_date',-29),1,0) flag
    from ${APP}.dws_course_user_course_com_rec_td
    where dt='$do_date'
  )t1
  group by user_id,course_id
)t2;

"
ads_course_chapter_complete_avg="
insert overwrite table ${APP}.ads_course_chapter_complete_avg
(
  select '$do_date' dt,
         1 recent_days,
         t1.course_id,
         count/user_count
  from (
    select course_id,
           count(distinct user_id) user_count
    from ${APP}.dws_course_user_chapter_com_rec_td
    where dt='$do_date'
    group by course_id
  )t1
  left join (
    select chapter_id,
           course_id,
           count(*) count
    from ${APP}.dws_course_user_chapter_com_rec_td
    where dt = '$do_date'
    group by chapter_id, course_id
  )t2
  on t1.course_id=t2.course_id
  union
  select '$do_date' dt,
         7 recent_days,
         t1.course_id,
         count/user_count
  from (
    select course_id,
           count(distinct user_id) user_count
    from ${APP}.dws_course_user_chapter_com_rec_td
    where dt >= date_add('$do_date', -6)
      and dt <= '$do_date'
    group by course_id
  )t1
  left join (
    select chapter_id,
           course_id,
           count(*) count
    from ${APP}.dws_course_user_chapter_com_rec_td
    where dt >= date_add('$do_date', -6)
      and dt <= '$do_date'
    group by chapter_id, course_id
  )t2
  on t1.course_id=t2.course_id
  union
  select '$do_date' dt,
         30 recent_days,
         t1.course_id,
         count/user_count
  from (
    select course_id,
           count(distinct user_id) user_count
    from ${APP}.dws_course_user_chapter_com_rec_td
    where dt >= date_add('$do_date', -29)
      and dt <= '$do_date'
    group by course_id
  )t1
  left join (
    select chapter_id,
           course_id,
           count(*) count
    from ${APP}.dws_course_user_chapter_com_rec_td
    where dt >= date_add('$do_date', -29)
      and dt <= '$do_date'
    group by chapter_id, course_id
  )t2
  on t1.course_id=t2.course_id
);
"
ads_user_retention="
insert overwrite table ${APP}.ads_user_retention
select * from ads_user_retention
union
select
    '$do_date' dt,
    login_date_first create_date,
    datediff('$do_date',login_date_first) retention_day,
    sum(if(login_date_last='$do_date',1,0)) retention_count,
    count(*) new_user_count,
    cast(sum(if(login_date_last='$do_date',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from
(
    select
        user_id,
        date_id login_date_first
    from ${APP}.dwd_user_register_inc
    where dt>=date_add('$do_date',-7)
    and dt<'$do_date'
)t1
join
(
    select
        user_id,
        login_date_last
    from ${APP}.dws_user_user_login_td
    where dt='$do_date'
)t2
on t1.user_id=t2.user_id
group by login_date_first;
"
case $1 in
    "ads_user_stats" )
        hive -e "$ads_user_stats"
    ;;
    "ads_course_review" )
        hive -e "$ads_course_review"
    ;;
    "ads_traffic_stats_by_channel" )
        hive -e "$ads_traffic_stats_by_channel"
    ;;
    "ads_page_path" )
        hive -e "$ads_page_path"
    ;;
    "ads_user_change" )
        hive -e "$ads_user_change"
    ;;
    "ads_user_action" )
        hive -e "$ads_user_action"
    ;;
    "ads_source_turning" )
        hive -e "$ads_source_turning"
    ;;
    "ads_new_buyer_stats" )
        hive -e "$ads_new_buyer_stats"
    ;;
    "ads_age_buyer_stats" )
        hive -e "$ads_age_buyer_stats"
    ;;
    "ads_subject_order" )
        hive -e "$ads_subject_order"
    ;;
    "ads_category_order" )
        hive -e "$ads_category_order"
    ;;
    "ads_course_try_and_buy" )
        hive -e "$ads_course_try_and_buy"
    ;;
    "ads_subject_try_and_buy" )
        hive -e "$ads_subject_try_and_buy"
    ;;
    "ads_category_try_and_buy" )
        hive -e "$ads_category_try_and_buy"
    ;;
    "ads_order_by_province" )
        hive -e "$ads_order_by_province"
    ;;
    "ads_trade_stats" )
        hive -e "$ads_trade_stats"
    ;;
    "ads_course_exam_stats_by_paper" )
        hive -e "$ads_course_exam_stats_by_paper"
    ;;
    "ads_course_exam_stats_by_course" )
        hive -e "$ads_course_exam_stats_by_course"
    ;;
    "ads_course_question_accuracy" )
        hive -e "$ads_course_question_accuracy"
    ;;
    "ads_course_paper_grade_section" )
        hive -e "$ads_course_paper_grade_section"
    ;;
    "ads_course_play_stats_by_chapter" )
        hive -e "$ads_course_play_stats_by_chapter"
    ;;
    "ads_course_play_stats_by_course" )
        hive -e "$ads_course_play_stats_by_course"
    ;;
    "ads_course_complete_stats_by_course" )
        hive -e "$ads_course_complete_stats_by_course"
    ;;
    "ads_course_chapter_complete_avg" )
        hive -e "$ads_course_chapter_complete_avg"
    ;;
    "ads_user_retention" )
        hive -e "$ads_user_retention"
    ;;
    "all" )
        hive -e "$ads_user_stats$ads_course_review$ads_traffic_stats_by_channel$ads_page_path$ads_user_change$ads_user_action$ads_source_turning$ads_new_buyer_stats$ads_age_buyer_stats$ads_subject_order$ads_category_order$ads_course_try_and_buy$ads_subject_try_and_buy$ads_category_try_and_buy$ads_order_by_province$ads_trade_stats$ads_course_exam_stats_by_paper$ads_course_exam_stats_by_course$ads_course_question_accuracy$ads_course_paper_grade_section$ads_course_play_stats_by_chapter$ads_course_play_stats_by_course$ads_course_complete_stats_by_course$ads_course_chapter_complete_avg$ads_user_retention"
    ;;
esac
